# -*- coding: utf-8 -*-
"""
Created on Fri Jan  8 14:50:58 2021

@author: PC
"""

import pymysql
import pandas as pd
from sqlalchemy import create_engine
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/aaa?charset=utf8')
df=pd.read_sql("select * from tbl_course",conn)
df=df[df['open_time']>'2019-03']
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('/',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
print(df)
df=df.drop('dependency',axis=1).join(df['dependency'].str.split('-',expand=True).stack().reset_index(level=1,drop=True).rename('dependency'))
df=df.reset_index()
print(df)
df=df.drop([4,5,8,9,17,18])
print(df)
df1=df['class_hours'].groupby(df['dependency']).count()
df2=df['lecturer_id'].groupby(df['dependency']).nunique()
df=pd.merge(df1,df2,how="inner",left_on='dependency',right_on='dependency')
df.columns=['total_count','uniqs']
data=df
df=data.sort_values('uniqs',ascending=True)
print(df)
df.to_sql('2B_yuyuehai3', conn, index= False)